/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pin.db.devel;

import pin.db.dbcp.*;
import java.io.File;
import java.io.FileWriter;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import pin.db.ConnectionPool;

/**
 *
 * @author didyhu
 */
public class DbMapping {

    public static void prepareJavaFile(Connection conn) throws Exception {
        Statement stmt = conn.createStatement();
        DatabaseMetaData dbmd = conn.getMetaData();
        ResultSet rsTable = conn.getMetaData().getTables(null, null, null, null);
        final String packageString = "pin.db.schema.base";
        final String pathString = "src/pin/db/schema/base";

        {
            String basePeerString = "package " + packageString + ";\n" + "import java.io.PrintStream;\n" + "import java.lang.reflect.Method;\n" + "import java.sql.Connection;\n" + "import java.sql.PreparedStatement;\n" + "import java.sql.ResultSet;\n" + "import java.sql.SQLException;\n" + "import java.sql.Statement;\n" + "public class BasePeer {\n" + "    public static void list(PrintStream out, Object object) throws Exception {\n" + "        Class cls=object.getClass();\n" + "        Method[] methods = cls.getMethods();\n" + "        \n" + "        for (int i = 1; i < methods.length; i++) {\n" + "            if (methods[i].getName().startsWith(\"get\") && !methods[i].getName().equals(\"getClass\") && methods[i].getParameterTypes().length<1) {\n" + "                out.println(methods[i].getName().substring(3) + \":\" + methods[i].invoke(object));\n" + "            }\n" + "        }\n" + "    }\n" + "    public static ResultSet executeQuery(String sql, Connection conn) throws SQLException {\n" + "        Statement stmt = conn.createStatement();\n" + "        ResultSet rs = stmt.executeQuery(sql);\n" + "        return rs;\n" + "    }\n" + "    public static ResultSet executeQuery(String tableName, String[] columns, String conditions, Object[] values, int offset, int limit,Connection conn) throws SQLException {\n" + "        StringBuffer columnsString = new StringBuffer();\n" + "        for (String column : columns) {\n" + "            columnsString.append(\",`\" + tableName + \"`.`\" + column + \"`\");\n" + "        }\n" + "        columnsString.deleteCharAt(0);\n" + "        String sql = String.format(\"SELECT %s FROM `%s` WHERE %s LIMIT %d,%d ;\",\n" + "                columnsString.toString(), tableName, conditions,offset,limit);\n" + "        System.out.println(sql);\n" + "        PreparedStatement pstmt = conn.prepareStatement(sql);\n" + "        if (values != null) {\n" + "            for (int i = 0; i < values.length; i++) {\n" + "                //values[i].getClass().getSimpleName();\n" + "                pstmt.setObject(i + 1, values[i]);\n" + "            }\n" + "        }\n" + "        ResultSet rs = pstmt.executeQuery();\n" + "        return rs;\n" + "    }\n" + "}\n";

            File cu = new File(pathString);
            String path = cu.getAbsolutePath();
            String fileName = "BasePeer.java";
            File javaFile = new File(path + "/" + fileName);
            FileWriter fw = new FileWriter(javaFile);
            fw.write(basePeerString);
            fw.close();
        }

        while (rsTable.next()) {
            final String tableNameDb,  tableNameClass,  tableNameClassBase,  tableNameVar,  columnsNameDb,  columnFields;
            final String tmpGettersAndSetters,  tmpFields,  tmpFieldsString,  tmpRsGetter,  tmpFK,  tmpEK,  tmpCon,  tmpCon2;











            Vector<String> primaryKeysCollection = new Vector<String>(),
                    columnsNameDbCollection = new Vector<String>();


            tableNameDb = rsTable.getString(3);
            tableNameClassBase = getNameClass(tableNameDb) + "Base";
            tableNameClass = getNameClass(tableNameDb);
            tableNameVar = getNameVar(tableNameClass);

            ResultSet rsRecord = stmt.executeQuery("SELECT * FROM `" + tableNameDb + "` LIMIT 1;");
            ResultSetMetaData rsmd = rsRecord.getMetaData();

            {
                ResultSet rsPK = dbmd.getPrimaryKeys(null, null, tableNameDb);

                while (rsPK.next()) {
                    primaryKeysCollection.add(rsPK.getString(4));
                }
            }








            {
                StringBuffer sbFields = new StringBuffer();
                sbFields.append("\tboolean[] modified=new boolean[" + rsmd.getColumnCount() + "];\n");
                sbFields.append("\tpublic void resetModified(){\n");
                sbFields.append("\t\tfor(boolean b:modified){\n");
                sbFields.append("\t\t\tb=false;\n");
                sbFields.append("\t\t}\n");
                sbFields.append("\t}\n");
                sbFields.append("\tprivate final Hashtable<String,Object> data=new Hashtable<String,Object>();\n");
                sbFields.append("\tpublic final void set(String columnNameDb, Object value){\n" +
                        "\t\tdata.put(columnNameDb,value);\n" +
                        "\t}\n");
                sbFields.append("\tpublic final Object get(String columnNameDb){\n" +
                        "\t\treturn data.get(columnNameDb);\n" +
                        "\t}\n");
                tmpFields = sbFields.toString();
            }
            {
                StringBuffer sbCon = new StringBuffer();
                StringBuffer sbCon2 = new StringBuffer();

                StringBuffer sbCon3 = new StringBuffer();
                StringBuffer sbCon4 = new StringBuffer();

                StringBuffer sbGettersAndSetters = new StringBuffer();
                StringBuffer sbColumnNames = new StringBuffer();
                //Vector<String> columnNames = new Vector<String>();
                StringBuffer sbColumnsNameDbString = new StringBuffer();
                StringBuffer sbObjectFiller = new StringBuffer();
                StringBuffer sbColumnsName = new StringBuffer();
                for (int column = 1; column < rsmd.getColumnCount() + 1; column++) {
                    final String columnTypeNameDb,  columnNameDb,  columnNameClass,  columnTypeNameClass,  columnTypeShortNameClass,  columnNameVar;

                    columnTypeNameDb = rsmd.getColumnTypeName(column);
                    columnNameDb = rsmd.getColumnLabel(column);
                    columnNameClass = getNameClass(columnNameDb);
                    columnTypeNameClass = convertToColumnTypeNameClass(rsmd.getColumnClassName(column));
                    columnTypeShortNameClass = convertToColumnTypeShortNameClass(columnTypeNameClass);
                    columnNameVar = getNameVar(columnNameClass);

                    //sbFields.append(String.format("\t//mysql type %s\n", columnTypeNameDb));
                    //sbFields.append(String.format("\tprivate %s %s=null;\n", columnTypeNameClass, columnNameDb));
                    sbCon.append(String.format(",%s %s", columnTypeNameClass, columnNameVar));
                    sbCon2.append(String.format("\t\tset%s(%s);\n", columnNameClass, columnNameVar));
                    sbCon3.append(String.format(",%s %s", columnTypeNameClass, columnNameVar));
                    sbCon4.append(String.format(",%s", columnNameVar));

                    sbColumnsName.append(String.format("\tpublic final String %s = \"%s.%s\";\n", columnNameClass.toUpperCase(), tableNameDb, columnNameDb));

                    sbGettersAndSetters.append(String.format("\tpublic void set%s(%s %s){\n" +
                            "\t\tif(%s!=null) {\n" +
                            "\t\t\tset(\"%s\",%s);\n" +
                            "\t\t\tthis.modified[" + (column - 1) + "] =true;\n\t\t}\n" +
                            "\t}\n", columnNameClass, columnTypeNameClass, columnNameVar, columnNameVar, columnNameDb, columnNameVar));
                    sbGettersAndSetters.append(String.format("\tpublic %s get%s(){\n" +
                            "\t\treturn (%s)get(\"%s\");\n" +
                            "\t}\n", columnTypeNameClass, columnNameClass, columnTypeNameClass, columnNameDb));

                    sbColumnNames.append("\tpublic static final String " + columnNameDb.toUpperCase() + " = \"" + columnNameDb + "\";\n");
                    columnsNameDbCollection.add(columnNameDb);
                    sbColumnsNameDbString.append(",\"" + columnNameDb + "\"");


                    String tmpClassName = columnTypeNameClass;
                    tmpClassName = lastWord(tmpClassName);

                    tmpClassName = (tmpClassName.equals("Integer")) ? "Int" : tmpClassName;
                    tmpClassName = (tmpClassName.equals("BigInteger")) ? "Long" : tmpClassName;
                    tmpClassName = (tmpClassName.equals("Date")) ? "Timestamp" : tmpClassName;
                    sbObjectFiller.append("\t\t\ttry{");
                    sbObjectFiller.append(firstLower(tableNameClass) + ".set" + columnNameClass + "(rs.get" + tmpClassName + "(\"" + columnNameDb + "\"));");
                    sbObjectFiller.append("}catch(Exception ex){}\n");

                }
                columnFields = sbColumnNames.toString();
                sbCon.deleteCharAt(0);


                sbCon.insert(0, String.format("\tpublic %sBase(", tableNameClass));
                sbCon.insert(0, String.format("\tpublic %sBase(){}\n", tableNameClass));
                sbCon.append("){\n");
                sbCon.append(sbCon2);
                sbCon.append("\t}\n");
                tmpCon = sbCon.toString();

                sbCon4.deleteCharAt(0);
                sbCon3.deleteCharAt(0);
                sbCon3.insert(0, String.format("\tpublic %s(", tableNameClass));
                sbCon3.insert(0, String.format("\tpublic %s(){}\n", tableNameClass));
                sbCon3.append("){\n");
                sbCon3.append("\t\tsuper(" + sbCon4 + ");\n");
                sbCon3.append("\t}\n");
                tmpCon2 = sbCon3.toString();


                sbColumnsNameDbString.deleteCharAt(0);
                sbColumnsNameDbString.insert(0, "\tpublic static final String[] columnNames=new String[]{");
                sbColumnsNameDbString.append("};\n");

                tmpGettersAndSetters = sbGettersAndSetters.toString();
                tmpFieldsString = sbColumnNames.toString() + sbColumnsNameDbString.toString();
                tmpRsGetter = sbObjectFiller.toString();
                columnsNameDb = sbColumnNames.toString();
            }
            {
                ResultSet exportedKeys = conn.getMetaData().getExportedKeys(null, null, tableNameDb);
                StringBuffer sbEK = new StringBuffer();
                while (exportedKeys.next()) {
                    String pkColumnName = exportedKeys.getString(4);
                    String fkColumnName = exportedKeys.getString(8);
                    String fkTableNameDB = exportedKeys.getString(7);
                    String fkTableNameClass = fkTableNameDB;

                    String[] tmp = fkTableNameClass.split("_");
                    fkTableNameClass = "";
                    for (int i = 0; i < tmp.length; i++) {
                        tmp[i] = tmp[i].substring(0, 1).toUpperCase() + tmp[i].substring(1);
                        fkTableNameClass += tmp[i];

                    }



                    // public Collection<UserBasic> getUserBasicCollection()
                    sbEK.append(String.format("\tpublic Collection<%s> get%sCollection(Connection conn) throws SQLException {\n", fkTableNameClass, fkTableNameClass));
                    {
                        StringBuffer sbCondition = new StringBuffer(" TRUE ");
                        StringBuffer sbValue = new StringBuffer();
                        for (int i = 0; i < primaryKeysCollection.size(); i++) {
                            sbCondition.append(String.format("AND `%s`=?", primaryKeysCollection.elementAt(i)));
                            sbValue.append(String.format(",this.get(\"%s\")", primaryKeysCollection.elementAt(i)));
                        }
                        sbValue.deleteCharAt(0);
                        sbEK.append(String.format("\t\tResultSet rs=BasePeer.executeQuery(%sPeer.tableName,%sPeer.columnNames,\"%s\",new Object[]{%s}, offset,  limit,conn);\n", fkTableNameClass, fkTableNameClass, sbCondition.toString(), sbValue.toString()));
                    }
                    sbEK.append(String.format("\t\tCollection<%s> collection=new Vector<%s>();\n", fkTableNameClass, fkTableNameClass));
                    sbEK.append(String.format("\t\t%sPeer.fill(rs,collection);\n", fkTableNameClass));
                    sbEK.append(String.format("\t\treturn collection;\n"));
                    sbEK.append(String.format("\t\t\n"));
                    sbEK.append(String.format("\t\t\n"));
                    sbEK.append(String.format("\t\t\n"));
                    sbEK.append(String.format("\t}\n"));


                }
                ///////////////////
                StringBuffer sbFK = new StringBuffer();
                ResultSet rsIP = dbmd.getImportedKeys(null, null, tableNameDb);
                while (rsIP.next()) {
                    String pkTableNameDb = rsIP.getString(3);
                    String pkTableNameClass = getNameClass(pkTableNameDb);

                    String pkColumnNameDb = rsIP.getString(4);
                    String pkColumnNameClass = firstUpper(pkColumnNameDb);
                    //String pkColumnTypeDB = rsIP.getString(3);
                    //String pkColumnTypeClass = rsIP.getString(3);
                    String fkTableNameDb = rsIP.getString(7);
                    String fkTableNameClass = getNameClass(fkTableNameDb);
                    String fkColumnNameDb = rsIP.getString(8);
                    String fkColumnNameClass = firstUpper(fkColumnNameDb);
                    String fkColumnNameVar = fkColumnNameDb;
                    //String fkColumnTypeDB = rsIP.getString(3);
                    //String fkColumnTypeClass = rsIP.getString(3);

                    sbFK.append(String.format("\tpublic %s get%sThrough%s(Connection conn) throws SQLException {\n", pkTableNameClass, pkTableNameClass, fkColumnNameClass));

                    sbFK.append(String.format("\t\t%s o = %sPeer.get%sBy%s(get%s(),conn);\n", pkTableNameClass, pkTableNameClass, pkTableNameClass, pkColumnNameClass, fkColumnNameClass));
                    sbFK.append(String.format("\t\treturn o;\n"));

                    sbFK.append(String.format("\t}\n"));



                }
                tmpFK = sbFK.toString();
                tmpEK = sbEK.toString();
            }
            {
                StringBuffer classSb = new StringBuffer();
                classSb.append(String.format("package pin.db.schema.base;\n" +
                        "import pin.db.schema.*;\nimport java.util.Hashtable;\nimport java.sql.Timestamp;\n" +
                        "import java.sql.Connection;\nimport java.sql.ResultSet;\nimport java.util.Collection;\nimport java.sql.SQLException;\nimport java.util.Vector;\n" +
                        "public class %sBase{\n", tableNameClass));
                classSb.append(tmpFields);
                classSb.append(tmpCon);
                classSb.append(tmpGettersAndSetters);
                classSb.append(tmpEK);
                classSb.append(tmpFK);
                classSb.append(String.format("}\n"));
                //System.out.println(classSb.toString());
                String path = "src/pin/db/schema/base";
                File cu = new File(path);
                path = cu.getAbsolutePath();
                String fileName = tableNameClass + "Base.java";
                File javaFile = new File(path + "/" + fileName);
                FileWriter fw = new FileWriter(javaFile);
                fw.write(classSb.toString());
                fw.close();
            }


            {
                StringBuffer sb = new StringBuffer();
                sb.append(String.format("package pin.db.schema;\n" +
                        "import pin.db.schema.base.*;\n" +
                        "public class %s extends %sBase{\n", tableNameClass, tableNameClass));
                sb.append(tmpCon2);
                sb.append(String.format("}\n"));
                //System.out.println(classSb.toString());
                String path = "src/pin/db/schema";
                File cu = new File(path);
                path = cu.getAbsolutePath();
                String fileName = tableNameClass + ".java";
                File javaFile = new File(path + "/" + fileName);
                FileWriter fw = new FileWriter(javaFile);
                fw.write(sb.toString());
                fw.close();
            }




////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
            {
                System.out.println("tableNameClass" + tableNameClass);
                StringBuffer utilsSb = new StringBuffer();
                utilsSb.append(String.format("package pin.db.schema.base;\n" +
                        "import pin.db.schema.*;\nimport java.util.Vector;\nimport java.util.Collection;\nimport java.sql.Connection;\nimport java.sql.PreparedStatement;\nimport java.sql.SQLException;\nimport java.sql.ResultSet;\n" +
                        "public class %sPeer extends BasePeer{\n", tableNameClass));
                utilsSb.append("\tpublic static final String tableName = \"" + tableNameDb + "\";\n");
                //utilsSb.append(columnFields);
                utilsSb.append(tmpFieldsString);
                //get by pk

                {
                    StringBuffer pkps = new StringBuffer();
                    for (String pk : primaryKeysCollection) {
                        pkps.append(String.format("Object %s,", pk));
                    }
                    StringBuffer sbCondition = new StringBuffer(" TRUE ");
                    StringBuffer sbValue = new StringBuffer();
                    for (int i = 0; i < primaryKeysCollection.size(); i++) {
                        sbCondition.append(String.format(" AND `%s`=? ", primaryKeysCollection.elementAt(i)));
                        sbValue.append(String.format("%s,", primaryKeysCollection.elementAt(i)));
                    }
                    //sbValue.deleteCharAt(sbValue.length());
                    utilsSb.append(String.format("\tpublic static %s get%sByPKs(%s Connection conn) throws SQLException {\n", tableNameClass, tableNameClass, pkps.toString()));
                    utilsSb.append(String.format("\t\tResultSet rs=BasePeer.executeQuery(%sPeer.tableName,%sPeer.columnNames,\"%s\",new Object[]{%s},0, 1,conn);\n",
                            tableNameClass, tableNameClass, sbCondition.toString(), sbValue.toString()));
                    utilsSb.append(String.format("\t\t\t%s %s = new %s();\n", tableNameClass, firstLower(tableNameClass), tableNameClass));
                    utilsSb.append(String.format("\t\tfill(rs,%s);\n", firstLower(tableNameClass)));
                    utilsSb.append(String.format("\t\treturn %s;\n", firstLower(tableNameClass)));
                    utilsSb.append(String.format("\t}\n"));
                }
                //get reference
                {

                    StringBuffer fkeys = new StringBuffer();
                    ResultSet rsFk = dbmd.getImportedKeys(null, null, tableNameDb);
                    Vector<String> fks = new Vector<String>();
                    while (rsFk.next()) {
                        String fkName = rsFk.getString(4);
                        if (fks.contains(fkName)) {
                            continue;
                        }
                        fkeys.append("\"" + fkName + "\",");
                    }


                    StringBuffer pkps = new StringBuffer();
                    for (String pk : primaryKeysCollection) {
                        pkps.append(String.format("Object %s,", pk));
                        fkeys.append("\"" + pk + "\",");
                    }
                    StringBuffer sbCondition = new StringBuffer(" TRUE ");
                    StringBuffer sbValue = new StringBuffer();
                    for (int i = 0; i < primaryKeysCollection.size(); i++) {
                        sbCondition.append(String.format("AND `%s`=?", primaryKeysCollection.elementAt(i)));
                        sbValue.append(String.format("%s,", primaryKeysCollection.elementAt(i)));
                    }
                    fkeys.insert(0, "new String[]{");
                    fkeys.append("}");

                    //sbValue.deleteCharAt(sbValue.length());
                    utilsSb.append(String.format("\tpublic static %s get%sReferenceColumnsOnlyByPKs(%s Connection conn) throws SQLException {\n", tableNameClass, tableNameClass, pkps.toString()));
                    utilsSb.append(String.format("\t\tResultSet rs=BasePeer.executeQuery(%sPeer.tableName,%s,\"%s\",new Object[]{%s}, 0,  1,conn);\n",
                            tableNameClass, fkeys, sbCondition.toString(), sbValue.toString()));
                    utilsSb.append(String.format("\t\t\t%s %s = new %s();\n", tableNameClass, firstLower(tableNameClass), tableNameClass));
                    utilsSb.append(String.format("\t\tfill(rs,%s);\n", firstLower(tableNameClass)));
                    utilsSb.append(String.format("\t\treturn %s;\n", firstLower(tableNameClass)));
                    utilsSb.append(String.format("\t}\n"));
                }

                ResultSet uniqueIndexInfo = conn.getMetaData().getIndexInfo(null, null, tableNameDb, true, false);
                Vector<String> uniqueIndexColumn = new Vector<String>();
                while (uniqueIndexInfo.next()) {
                    if (!uniqueIndexColumn.contains(uniqueIndexInfo.getString(9))) {
                        uniqueIndexColumn.add(uniqueIndexInfo.getString(9));

                        final String columnNameDb,  columnNameClass,  columnTypeNameClass,  columnTypeShortNameClass;
                        columnNameDb = uniqueIndexInfo.getString(9);
                        columnNameClass = getNameClass(columnNameDb);
                        {
                            String tmp = null;
                            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                                if (columnNameDb.equals(rsmd.getColumnName(i))) {
                                    tmp = convertToColumnTypeNameClass(rsmd.getColumnClassName(i));
                                    break;
                                }
                            }
                            if (tmp == null) {
                                continue;
                            }
                            columnTypeNameClass = tmp;
                        }
                        columnTypeShortNameClass = convertToColumnTypeShortNameClass(columnTypeNameClass);//columnTypeNameClass;

                        {
                            utilsSb.append(String.format("\tpublic static %s get%sBy%s(%s %s, Connection conn) throws SQLException {\n", tableNameClass, tableNameClass, columnNameClass, columnTypeShortNameClass, columnNameDb));
                            utilsSb.append(String.format("\t\tResultSet rs=BasePeer.executeQuery(%sPeer.tableName,%sPeer.columnNames,\"`%s`=?\",new Object[]{%s},0, 1,conn);\n",
                                    tableNameClass, tableNameClass, columnNameDb, columnNameDb));
                            utilsSb.append(String.format("\t\t\t%s %s = new %s();\n", tableNameClass, tableNameVar, tableNameClass));
                            utilsSb.append(String.format("\t\tfill(rs,%s);\n", tableNameVar));
                            utilsSb.append(String.format("\t\treturn %s;\n", tableNameVar));

                            utilsSb.append(String.format("\t}\n"));
                        }
                    }

                }

                StringBuffer sbRsFiller = new StringBuffer();
                {
                    sbRsFiller.append(String.format("\tpublic static void fill(ResultSet rs, %s %s) throws SQLException {\n", tableNameClass, tableNameVar));
                    sbRsFiller.append(String.format("\t\tif(rs.next()){\n"));

                    sbRsFiller.append(tmpRsGetter);

                    sbRsFiller.append(String.format("\t\t\t%s.resetModified();\n", tableNameVar));
                    sbRsFiller.append(String.format("\t\t}\n"));
                    sbRsFiller.append(String.format("\t}\n"));

                    sbRsFiller.append(String.format("\tpublic static void fill(ResultSet rs, Collection<%s> %sCollection) throws SQLException {\n", tableNameClass, tableNameVar));

                    sbRsFiller.append(String.format("\t\twhile(rs.next()){\n"));
                    sbRsFiller.append(String.format("\t\t\t%s %s=new %s();\n", tableNameClass, tableNameVar, tableNameClass));
                    sbRsFiller.append(tmpRsGetter);
                    sbRsFiller.append(String.format("\t\t\t%s.resetModified();\n", tableNameVar));
                    sbRsFiller.append(String.format("\t\t\t%sCollection.add(%s);\n", tableNameVar, tableNameVar));

                    sbRsFiller.append(String.format("\t\t}\n"));
                    sbRsFiller.append(String.format("\t}\n"));

                }
                {


                    sbRsFiller.append(String.format("\tpublic static boolean update(%s %s, Connection conn) throws SQLException {\n", tableNameClass, tableNameVar));

                    sbRsFiller.append(String.format("\t\tVector<String> columns = new Vector<String>();\n"));
                    sbRsFiller.append(String.format("\t\tfor (int i = 0; i < %sPeer.columnNames.length; i++) {\n", tableNameClass));
                    sbRsFiller.append(String.format("\t\t\tif (((%sBase)%s).modified[i]) {\n", tableNameClass, tableNameVar));
                    sbRsFiller.append(String.format("\t\t\t\tcolumns.add(%sPeer.columnNames[i]);\n", tableNameClass));

                    sbRsFiller.append(String.format("\t\t\t}\n"));

                    sbRsFiller.append(String.format("\t\t}\n"));
                    sbRsFiller.append(String.format("\t\tif (columns.isEmpty()) return false;\n"));
                    sbRsFiller.append(String.format("\t\tStringBuffer sql = new StringBuffer();\n" +
                            "\t\tfor (String column : columns) {" +
                            "\t\t\tsql.append(\",`\"+column+\"` = ? \");" +
                            "\t\t}\n" +
                            "\t\tsql.deleteCharAt(0);\n" +
                            "\t\tsql.insert(0, \"UPDATE `%s` SET \");\n", tableNameDb));
                    StringBuffer wc = new StringBuffer();
                    for (String pk : primaryKeysCollection) {
                        wc.append("AND `" + pk + "` = ? ");
                    }
                    //System.out.println(wc.toString());
                    String wcs = "WHERE TRUE " + wc.toString();


                    sbRsFiller.append(String.format("\t\tsql.append(\"%s\");\n", wcs));
                    sbRsFiller.append(String.format("\t\tPreparedStatement pstmt=conn.prepareStatement(sql.toString());\n"));
                    sbRsFiller.append(String.format("\t\tfor (int i=0;i<columns.size();i++) {\n"));

                    sbRsFiller.append(String.format("\t\t\tString column = columns.elementAt(i);\n" +
                            "\t\t\tpstmt.setObject(i+1,%s.get(column));\n", tableNameVar));
                    sbRsFiller.append(String.format("\t\t}\n"));
                    for (int i = 0; i < primaryKeysCollection.size(); i++) {
                        String pk = primaryKeysCollection.elementAt(i);
                        sbRsFiller.append(String.format("\t\tpstmt.setObject(columns.size()+1+%d,%s.get(\"%s\"));\n", i, tableNameVar, pk));
                    }

                    sbRsFiller.append(String.format("\t\tint affected=pstmt.executeUpdate();\n" +
                            "\t\treturn affected>0;\n"));


                    sbRsFiller.append(String.format("\t}\n"));
                }
                /*
                StringBuffer sql1 = new StringBuffer();
                StringBuffer sql2 = new StringBuffer();
                for (String column : columnNames) {
                if (userUploadSpot.get(column) != null) {
                sql1.append("," + column);
                sql2.append(", ? ");
                }
                }
                sql1.deleteCharAt(0);
                sql2.deleteCharAt(0);
                String sql = "INSERT INTO `_User_UploadSpot` (" + sql1.toString() + ") VALUES (" + sql2.toString() + ") ";
                PreparedStatement pstmt = conn.prepareStatement(sql);
                for (int i = 0; i < columnNames.length; i++) {
                String column = columnNames[i];
                if (userUploadSpot.get(column) != null) {
                pstmt.setObject(i + 1, userUploadSpot.get(column));
                }
                }
                int affected = pstmt.executeUpdate();
                return affected > 0;*/
                {
                    sbRsFiller.append(String.format("\tpublic static ResultSet insert(%s %s, Connection conn) throws SQLException {\n", tableNameClass, tableNameVar));

                    sbRsFiller.append(String.format("\t\tStringBuffer sql1 = new StringBuffer();\n"));
                    sbRsFiller.append(String.format("\t\tStringBuffer sql2 = new StringBuffer();\n"));
                    sbRsFiller.append(String.format("\t\tfor (String column : columnNames) {\n"));
                    sbRsFiller.append(String.format("\t\t\tif (%s.get(column) != null) {\n", tableNameVar));
                    sbRsFiller.append(String.format("\t\t\t\tsql1.append(\",`\" + column+\"`\");\n"));
                    sbRsFiller.append(String.format("\t\t\t\tsql2.append(\", ? \");\n"));
                    sbRsFiller.append(String.format("\t\t\t}\n"));
                    sbRsFiller.append(String.format("\t\t}\n"));
                    sbRsFiller.append(String.format("\t\tsql1.deleteCharAt(0);\n"));
                    sbRsFiller.append(String.format("\t\tsql2.deleteCharAt(0);\n"));
                    sbRsFiller.append(String.format("\t\tString sql = \"INSERT INTO `%s` (\" + sql1.toString() + \") VALUES (\" + sql2.toString() + \") \";\n", tableNameDb));
                    sbRsFiller.append(String.format("\t\tSystem.out.println(sql);\n"));

                    sbRsFiller.append(String.format("\t\tPreparedStatement pstmt = conn.prepareStatement(sql);\n"));
                    sbRsFiller.append(String.format("\t\tfor (int i = 0, j = 0; i < columnNames.length; i++) {\n"));
                    sbRsFiller.append(String.format("\t\t\tString column = columnNames[i];\n"));
                    sbRsFiller.append(String.format("\t\t\tSystem.out.println(column);\n"));
                    sbRsFiller.append(String.format("\t\t\tif (%s.get(column) != null) {\n", tableNameVar));
                    sbRsFiller.append(String.format("\t\t\t\tpstmt.setObject(i + 1 + j, %s.get(column));\n", tableNameVar));
                    sbRsFiller.append(String.format("\t\t\t}else{\n"));
                    sbRsFiller.append(String.format("\t\t\t\tj--;\n"));
                    sbRsFiller.append(String.format("\t\t\t}\n"));
                    sbRsFiller.append(String.format("\t\t}\n"));

                    sbRsFiller.append(String.format("\t\tint affected=pstmt.executeUpdate();\n"));
                    sbRsFiller.append(String.format("\t\treturn pstmt.getGeneratedKeys();\n"));

                    sbRsFiller.append(String.format("\t}\n"));
                }

                sbRsFiller.append(String.format("\tpublic static Collection<%s> get%sCollection(String conditions, Object[] values, int offset, int limit, Connection conn) throws SQLException {\n", tableNameClass, tableNameClass));
                sbRsFiller.append(String.format("\t\tResultSet rs=BasePeer.executeQuery(tableName,columnNames,conditions,values, offset,  limit,conn);\n"));
                sbRsFiller.append(String.format("\t\tCollection<%s> collection=new Vector<%s>();\n", tableNameClass, tableNameClass));
                sbRsFiller.append(String.format("\t\tfill(rs,collection);\n"));
                sbRsFiller.append(String.format("\t\treturn collection;\n"));
                sbRsFiller.append(String.format("\t}\n"));




                utilsSb.append(sbRsFiller);








                ///////////////






                utilsSb.append(String.format("}\n"));
                //System.out.println(utilsSb.toString());
                String path = "src/pin/db/schema/base";
                File cu = new File(path);
                path = cu.getAbsolutePath();
                String fileName = tableNameClass + "Peer.java";
                File javaFile = new File(path + "/" + fileName);
                FileWriter fw = new FileWriter(javaFile);
                fw.write(utilsSb.toString());
                fw.close();













                rsRecord.close();
            }

        }
        rsTable.close();
        stmt.close();


    }

    public static void main(String[] args) throws Exception {
        Connection conn = ConnectionPool.getConnection();

        try {
            prepareJavaFile(conn);

        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            ConnectionPool.getDataSource().stop();
        }
    //prepareUtilsFile();


    }

    private static String convertToColumnTypeNameClass(String cls) {
        String tmp = cls;
        tmp = (cls.equals("[b")) ? "byte[]" : tmp;
        tmp = (cls.equals("[B")) ? "java.sql.Blob" : tmp;
        tmp = (cls.equals("java.math.BigInteger")) ? "java.lang.Long" : tmp;
        tmp = (cls.equals("java.sql.Timestamp")) ? "java.util.Date" : tmp;

        return tmp;
    }

    private static String convertToColumnTypeShortNameClass(String name) {
        String tmp = name;
        tmp = (tmp.equals("java.lang.Integer")) ? "int" : tmp;
        tmp = (tmp.equals("java.lang.Boolean")) ? "boolean" : tmp;
        tmp = (tmp.equals("java.lang.Long")) ? "long" : tmp;
        tmp = (tmp.equals("java.lang.Double")) ? "double" : tmp;
        tmp = (tmp.equals("java.lang.Float")) ? "float" : tmp;
        return tmp;
    }

    private static String firstUpper(String str) {
        if (str.length() > 1) {
            return str.substring(0, 1).toUpperCase() + str.substring(1);
        } else {
            return str.toUpperCase();
        }
    }

    private static String firstLower(String str) {
        if (str.length() > 1) {
            return str.substring(0, 1).toLowerCase() + str.substring(1);
        } else {
            return str.toLowerCase();
        }
    }

    private static String lastWord(String str) {
        int index = str.lastIndexOf(".");
        return str.substring(index + 1);
    }

    private static String getNameClass(String nameDb) {
        return removeSpliter(nameDb);
    }

    private static String getNameVar(String nameClass) {
        return firstLower(nameClass);
    }

    private static String removeSpliter(String var) {
        String tmp = var;
        {
            StringBuffer tmp1 = new StringBuffer();
            String[] tmp2;
            tmp2 = tmp.split("_");
            for (int i = 0; i < tmp2.length; i++) {
                tmp2[i] = firstUpper(tmp2[i]);
                tmp1.append(tmp2[i]);
            }
            tmp = tmp1.toString();
        }
        /*
        {
        StringBuffer tmp1 = new StringBuffer();
        String[] tmp2;
        tmp2 = tmp.split(".");
        for (int i = 0; i < tmp2.length; i++) {
        tmp2[i] = firstUpper(tmp2[i]);
        tmp1.append(tmp2[i]);
        }
        tmp = tmp1.toString();
        }
        {
        StringBuffer tmp1 = new StringBuffer();
        String[] tmp2;
        tmp2 = tmp.split("-");
        for (int i = 0; i < tmp2.length; i++) {
        tmp2[i] = firstUpper(tmp2[i]);
        tmp1.append(tmp2[i]);
        }
        tmp = tmp1.toString();
        }*/
        System.out.println(var + "=>" + tmp);
        return tmp;
    }

    public static void listResultSet(ResultSet rs, int limit, PrintStream out) throws SQLException {
        ResultSetMetaData rsmd = rs.getMetaData();
        for (int j = 0; j < limit && rs.next(); j++) {
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                out.print(rsmd.getColumnName(i) + ":" + rs.getObject(i) + "\n");
            }
            out.println("============");
        }

    }
}
